package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Store;
public class UserMoneyService {
	/**
     * 分公司列表
     * @return
     */
	public List<Store> findStoreList() {
		return Store.me().find("select id,storeid,title from t_dict_store");
	}
	 /**
     * 根据用户ID查询所属的公司id
     * @param userid
     * @return
     */
    public String findSellerId(Integer userid){
    	return Db.queryStr("select A.sellerId from t_bus_seller A where A.sellerId=(select B.sellerId from t_dict_store B where B.storeid=(select C.storeid from t_auth_admin_seller C where C.id=?))",userid);
    }

	/**
	 * 钱包明细
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param beginDate
	 * @param endDate
	 * @param stor
	 * @param dao
	 * @param taici
	 * @param state

	 * @return
	 */
	public Page<Record> findByPage(int pageNum, int pageSize,String phone,String name, 
			String beginDate, String endDate, String types,String storeIds){
		StringBuffer sBuffer = new StringBuffer("from t_bus_user_money A "
				+ "left join t_bus_user B on B.id=A.userid "
				+ "left join t_dict_store ST on ST.id=B.toBossTwoId "
				+ "where 1=1   ");
		List<Object> params = new ArrayList<Object>();
		
		if(StrKit.notBlank(storeIds)){
			sBuffer.append(" and ST.id in ('"+storeIds+"')");
		}
		

		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND B.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" and  B.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(A.createDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(types)){
			switch (types) {
			    //充值
				case "1":
						sBuffer.append(" AND A.orderType=2");
					break;
				//消费
				case "2":
						sBuffer.append(" AND A.orderType=1 AND A.type=2");
					break;
					
				//退款
				case "3":
					sBuffer.append(" AND A.orderType=1 AND A.type=1");
					break;	
	
			}
		}
		
		sBuffer.append(" ORDER BY A.createDate DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.nowAmount,A.amount,A.type,A.createDate,A.orderType,B.name,B.phone,B.id,B.toBossTwoId,ST.title ", sBuffer.toString(),params.toArray());
	}
	
	
	/**
	 * 总金额
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param name
	 * @param beginDate
	 * @param endDate
	 * @param stores
	 * @param types
	 * @return
	 */
	//查询增加的金额
	public Record findByMoney( String phone,String name, 
			String beginDate, String endDate,String types, String title){
		
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();

		sBuffer.append("SELECT  SUM(A.amount) money from t_bus_user_money A left join t_bus_user_wx B on B.id=A.userid left join t_bus_user C on C.id=B.userid where A.type=1 ");
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND C.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" and C.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(A.createDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and C.storeName in ('"+title+"')");
		}
		if(StrKit.notBlank(types)){
			switch (types) {
			    //充值
				case "1":
						sBuffer.append(" AND A.orderType=2");
					break;
				//消费
				/*case "2":
						sBuffer.append(" AND A.orderType=1 AND A.type=2");
					break;*/
					
				//退款
				case "3":
					sBuffer.append(" AND A.orderType=1 AND A.type=1");
					break;	
	
			}
		}
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;
	}
	//查询减少的金额
	public Record findByMoney1( String phone,String name, 
			String beginDate, String endDate,String types, String title){
		
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();

		sBuffer.append("SELECT  SUM(A.amount) money from t_bus_user_money A left join t_bus_user_wx B on B.id=A.userid left join t_bus_user C on C.id=B.userid where A.type=2 ");
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND C.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" and C.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(A.createDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and C.storeName in ('"+title+"')");
		}
		if(StrKit.notBlank(types)){
			switch (types) {
			    //充值
				/*case "1":
						sBuffer.append(" AND A.orderType=2");
					break;*/
				//消费
				case "2":
						sBuffer.append(" AND A.orderType=1 AND A.type=2");
					break;
					
				//退款
				/*case "3":
					sBuffer.append(" AND A.orderType=1 AND A.type=1");
					break;*/	
	
			}
		}
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;
	}
	
	
	
	
	/**
	 * 数据导出
	 * @param phone
	 * @param name
	 * @param beginDate
	 * @param endDate
	 * @param stores
	 * @param types
	 * @return
	 */
	public List<Record> findExcelVipByPage( String phone,String name, 
			String beginDate, String endDate, String types,String title) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.amount,A.type,A.createDate,A.orderType,B.name,B.phone,B.id,ST.title as store,ST.title from t_bus_user_money A "
				+ "left join t_bus_user B on B.id=A.userid "
				+ "left join t_dict_store ST on ST.id=B.toBossTwoId "
				+ "where 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND B.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" and B.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append("AND DATE_FORMAT(A.createDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		//类别
		if(StrKit.notBlank(types)){
			switch (types) {
			    //充值
				case "1":
						sBuffer.append(" AND A.orderType=2");
					break;
				//消费
				case "2":
						sBuffer.append(" AND A.orderType=1 AND A.type=2");
					break;
					
				//退款
				case "3":
					sBuffer.append(" AND A.orderType=1 AND A.type=1");
					break;	
	
			}
		}
		//分公司
		/*if(!StrKit.isBlank(stores)&&!"0".equals(stores)&& !"undefined".equals(stores)){
			sBuffer.append(" AND A.storeid in ('"+stores+"')");
		}*/
		if(StrKit.notBlank(title)){
			sBuffer.append(" and D.title in ('"+title+"')");
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}

	
	/**
	 * 数据导出
	 */
	public void exportVip(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "钱包明细", titles, listz);
	}
}